/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.montoroTurismo.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.montoroTurismo.beans.OffersBean;

/**
 *
 * @author Antonio
 */
public class AccesoBD {

    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    private java.sql.Date sqlStartDate;
    private java.sql.Date sqlSEndDate;
    private String INSERT_OFFER = "insert into ofertas (usuario, titulo, fechaFin, descripcion) values (?, ?, ?, ?)";
    private String SELECT_OFFER = "select id , titulo, fechaFin, descripcion from ofertas where usuario = ?";
    private String UPDATE_OFFER = "UPDATE ofertas SET titulo= ? , fechaFin= ? , descripcion= ? where usuario= ? and id= ?";
    private String DELETE_OFFER = "DELETE FROM ofertas WHERE id=?";

    public AccesoBD() throws Exception {
        try {
            // Se registra el Driver de MySQL
            DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());

            // Se obtiene una conexión con la base de datos. Hay que
            // cambiar el usuario "root" y la clave "la_clave" por las
            // adecuadas a la base de datos que estemos usando.
            connect = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/webappmontturismo", "root", "");

            // Se crea un Statement, para realizar la consulta
            statement = connect.createStatement();

        } catch (Exception e) {
            System.out.println("ERROR DE CONECCION A BBDD ");
            throw e;
        }
    }

    public ResultSet executeQueryDataBase(String sql) throws Exception {
        try {
            // Result set get the result of the SQL query
            resultSet = statement
                    .executeQuery(sql);
        } catch (Exception e) {
            throw e;
        }
        return resultSet;

    }

    public void insertOffer(OffersBean offer, String username) throws SQLException {

        convertToDate(offer.getDateEnd());


        preparedStatement = connect.prepareStatement(INSERT_OFFER);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, offer.getTitulo());
        preparedStatement.setDate(3, sqlSEndDate);
        preparedStatement.setString(4, offer.getDescripcion());
        //            preparedStatement.setBinaryStream(4, fileInputStream, (int) file.length());
        preparedStatement.execute();

    }

    public ResultSet selectOfferByUsername(String username) throws SQLException {
        preparedStatement = connect.prepareStatement(SELECT_OFFER);
        preparedStatement.setString(1, username);
        ResultSet rs = preparedStatement.executeQuery();
//        preparedStatement.close();
        return rs;
    }

    public void updateOffer(OffersBean offer, String username) throws SQLException {

        convertToDate(offer.getDateEnd());

        preparedStatement = connect.prepareStatement(UPDATE_OFFER);
        preparedStatement.setString(1, offer.getTitulo());
        preparedStatement.setDate(2, sqlSEndDate);
        preparedStatement.setString(3, offer.getDescripcion());
        preparedStatement.setString(4, username);
        preparedStatement.setInt(5, offer.getId());
        preparedStatement.execute();

    }
    // You need to close the resultSet

    public void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (preparedStatement != null) {
                preparedStatement.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connect != null) {
                connect.close();
            }
        } catch (Exception e) {
        }
    }

    public void deleteOffer(int id) throws SQLException {
        preparedStatement = connect.prepareStatement(DELETE_OFFER);
        preparedStatement.setInt(1, id);
        preparedStatement.execute();
    }

    private void convertToDate(String fechaEnd) {
        try {
            SimpleDateFormat sdf1 = new SimpleDateFormat("dd/MM/yyyy");
            java.util.Date dateEnd = sdf1.parse(fechaEnd);
            sqlSEndDate = new Date(dateEnd.getTime());
        } catch (ParseException ex) {
            Logger.getLogger(AccesoBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
